﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Xml.XPath;
using NUnit.Framework;
using Rhino.Mocks;
using Rhino.Mocks.Constraints;

namespace IPAMS.Data
{
    /// <summary>
    /// Loads and process Excel file 
    /// </summary>
	public class ExcelLoader : IDataLoader
	{
		private readonly IMapper mapper;
		private readonly string sourceFilePath;

        /// <summary>
        /// Initializes a new instance of the <see cref="ExcelLoader"/> class.
        /// </summary>
        /// <param name="sourceFilePath">The source file path.</param>
        /// <param name="mapper">The mapper.</param>
        /// <remarks>
        /// The mapper which the constructor uses maps tabs of the excel to tables in the data set and columns from the tabs into table columns.
        /// </remarks>
		public ExcelLoader(string sourceFilePath, IMapper mapper)
		{
			this.sourceFilePath = sourceFilePath;
			this.mapper = mapper;
		}

		#region IDataLoader Members

		public IPAMSDataSet Load()
		{
			IPAMSDataSet ipamsDataSet;
			using (DataSet dataSet = GetWorkbookAsDataSet())
			{
				ipamsDataSet = new IPAMSDataSet();

				foreach (DataTable targetTable in ipamsDataSet.Tables)
				{
					if (!dataSet.Tables.Contains(mapper.FindOriginalTableName(targetTable)))
						throw new StrongTypingException(
							string.Format("Missing table {0}",
							              mapper.FindOriginalTableName(targetTable)));
					foreach (DataColumn targetColumn in targetTable.Columns)
						if (!dataSet.Tables[mapper.FindOriginalTableName(targetTable)].Columns.Contains(mapper.FindOriginalColumnName(targetColumn)))
							throw new StrongTypingException(
								string.Format("In table {0} missing column {1}",
								              mapper.FindOriginalTableName(targetTable),
								              mapper.FindOriginalColumnName(targetColumn)));
				}

				TranslateDateSet(dataSet);

				string[] tablesInOrder;
				using (StreamReader reader = File.OpenText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TablesOrder.txt")))
				{
					string tablesNames = reader.ReadToEnd();
					tablesInOrder = tablesNames.Split(new string[] {Environment.NewLine},StringSplitOptions.RemoveEmptyEntries);
				}

				foreach (string tableName in tablesInOrder)

				{
					/*foreach (DataColumn targetColumn in ipamsDataSet.Tables[tableName].Columns)
						if (dataSet.Tables[tableName].Columns.Contains(targetColumn.ColumnName))
							dataSet.Tables[tableName].Columns[targetColumn.ColumnName].DataType =
								targetColumn.DataType;*/
					/*try
					{
						ipamsDataSet.Merge(dataSet.Tables[tableName], false, MissingSchemaAction.Ignore);
					}
					catch (ConstraintException e)
					{
						e.Data.Add("Table", mapper.FindOriginalTableName(dataSet.Tables[tableName]));
						throw;
					}*/
					foreach (DataRow row in dataSet.Tables[tableName].Rows)
					{
						if (NotEmpty(row))
							try
							{
								DataRow newRow = ipamsDataSet.Tables[tableName].NewRow();
								foreach (DataColumn column in ipamsDataSet.Tables[tableName].Columns)
									newRow[column.ColumnName] = row[column.ColumnName];
								ipamsDataSet.Tables[tableName].Rows.Add(newRow);

								//ipamsDataSet.Merge(new DataRow[]{row}, false, MissingSchemaAction.Ignore);
							}
							catch (ConstraintException e)
							{
								e.Data.Add("Table", mapper.FindOriginalTableName(dataSet.Tables[tableName]));
								e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
								throw;
							}
							catch (InvalidConstraintException e)
							{
								e.Data.Add("Table",
										   mapper.FindOriginalTableName(dataSet.Tables[tableName]));
								e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
								throw;
							}
							catch (ArgumentException e)
							{
								e.Data.Add("Table",
										   mapper.FindOriginalTableName(dataSet.Tables[tableName]));
								e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
								throw;
							}
							catch (NoNullAllowedException e)
							{
								e.Data.Add("Table",
								           mapper.FindOriginalTableName(dataSet.Tables[tableName]));
								e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
								throw;
							}
					}					
				}
			}
			return ipamsDataSet;
		}

		static bool NotEmpty(DataRow row)
		{
			foreach (object o in row.ItemArray)
				if (o != null && !string.IsNullOrEmpty(o.ToString().Trim()))
					return true;
			return false;
		}

/*
		private static void RemoveEmptyRows(DataSet dataSet)
		{
			foreach (DataTable table in dataSet.Tables)
				for (int i = table.Rows.Count-1; i >= 0; i--)
				{
					bool toDelete = true;
					foreach (object o in table.Rows[i].ItemArray)
						if(o!=null && !string.IsNullOrEmpty(o.ToString().Trim()))
						{
							toDelete = false;
							break;
						}
					if(toDelete)
						table.Rows.RemoveAt(i);
					else 
						table.Rows[i].SetAdded();
				}
			//dataSet.AcceptChanges();
		}
*/

		private void TranslateDateSet(DataSet dataSet)
		{
			foreach (DataTable table in dataSet.Tables)
			{
				foreach (DataColumn column in table.Columns)
					column.ColumnName = mapper.MapColumnName(column);
				table.TableName = mapper.MapTableName(table);
			}
		}

		#endregion

		private DataSet GetWorkbookAsDataSet()
		{
			DataSet excelDataSet;
			using (OleDbConnection excelConnection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", sourceFilePath)))
			{
				using (OleDbCommand excelCommand = new OleDbCommand())
				{
					excelCommand.Connection = excelConnection;
					using (OleDbDataAdapter excelAdapter = new OleDbDataAdapter(excelCommand))
					{
						excelConnection.Open();
						using (DataTable excelSheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"}))
						{
							excelDataSet = new DataSet();

							foreach (DataRow tabNameRow in excelSheets.Rows)
							{
								string tabName = (string) tabNameRow["TABLE_NAME"];
								if( tabName.EndsWith("_"))
									continue;
								DataTable tabTable =
									excelDataSet.Tables.Add(tabName.Remove(tabName.Length - 1, 1));
								excelCommand.CommandText = string.Format(@"SELECT * FROM [{0}]", tabName);
								try
								{
									excelAdapter.Fill(tabTable);
								}
								catch (ArgumentException e)
								{
									e.Data.Add("Table", tabTable.TableName);
									throw;
								}
							}
						}
					}
				}

				excelConnection.Close();
			}
			return excelDataSet;
		}
	}
}